import re
import time
from datetime import datetime
from typing import Type, Any, Sequence, Iterable

from pydantic import BaseModel
from sqlalchemy import select, func, Enum, DateTime, Select, desc, asc, delete, update, BigInteger, Update, Delete
from sqlalchemy.exc import IntegrityError, MultipleResultsFound, NoResultFound
from sqlalchemy.ext.declarative import DeclarativeMeta as Model
from sqlalchemy.orm import ColumnProperty, RelationshipProperty, Mapped, mapped_column, selectinload

from rosa.love_is_blue.backend.crud import get_pk_type
from rosa.love_is_blue.backend.memory.memory_crud import MemoryCRUDRouter
from rosa.love_is_blue.backend.sqlalchemy.sqlalchemy_tools import sql_helper, ModelBase
from rosa.love_is_blue.snowflake import snow
from rosa.love_is_blue.types import RecordStatus, DEPENDENCIES, GenerateRouterParams, PAGINATION, PayloadData, GenericBaseResponse, GetAllData, BaseResponse, PaginationData, \
    SiteException, StatusMap, logger, REAL_DELETE
from rosa.love_is_blue.utils import is_superuser, get_now_time
from rosa.utils import REQUEST_CONTEXT


class ValidationError(Exception):
    pass


class SiteBaseModel(ModelBase):
    __abstract__ = True

    id: Mapped[int] = mapped_column("id", BigInteger, primary_key=True, default=snow.get_id, comment="索引")
    # 如果是postgres数据库，需要先手动创建enum类型
    # CREATE TYPE RecordStatus AS ENUM ('active', 'inactive', 'frozen', 'obsolete');
    status: Mapped[RecordStatus] = mapped_column("status", Enum(RecordStatus), default=RecordStatus.ACTIVE, comment="数据状态")
    level: Mapped[int] = mapped_column("level", BigInteger, default=lambda: int(time.time() * 1000 - 1673366400000), comment="等级", index=True)
    create_time: Mapped[datetime] = mapped_column("create_time", DateTime, default=get_now_time, comment="创建时间")
    update_time: Mapped[datetime] = mapped_column("update_time", DateTime, default=get_now_time, onupdate=get_now_time, comment="更新时间")


class SQLAlchemyCRUDRouter(MemoryCRUDRouter):
    def __init__(
            self,
            db_model: Type[Model],
            schema: Type[BaseModel],
            create_schema: Type[BaseModel] | None = None,
            update_schema: Type[BaseModel] | None = None,
            prefix: str | None = None,
            tags: list[str] | None = None,
            paginate: int | None = None,
            get_all_route: bool | DEPENDENCIES = True,
            get_one_route: bool | DEPENDENCIES = True,
            create_route: bool | DEPENDENCIES = True,
            update_route: bool | DEPENDENCIES = True,
            delete_one_route: bool | DEPENDENCIES = True,
            delete_all_route: bool | DEPENDENCIES = False,
            get_all_route_params: GenerateRouterParams | None = None,
            get_one_route_params: GenerateRouterParams | None = None,
            create_route_params: GenerateRouterParams | None = None,
            update_route_params: GenerateRouterParams | None = None,
            delete_one_route_params: GenerateRouterParams | None = None,
            delete_all_route_params: GenerateRouterParams | None = None,
            verbose_name: str = '',
            verbose_name_plural: str = '',
            delete_update_field: str = '',
            strict_authentication: bool = False,
            **kwargs: Any
    ) -> None:
        self.db_model = db_model
        self.db_func = sql_helper.get_session
        self._primary_key = db_model.__table__.primary_key.columns.keys()[0]
        self._primary_key_type = get_pk_type(schema, self._primary_key)

        super().__init__(
            schema=schema,
            create_schema=create_schema,
            update_schema=update_schema,
            prefix=prefix or db_model.__tablename__.replace("_", "-"),
            tags=tags,
            paginate=paginate,
            get_all_route=get_all_route,
            get_one_route=get_one_route,
            create_route=create_route,
            update_route=update_route,
            delete_one_route=delete_one_route,
            delete_all_route=delete_all_route,
            get_all_route_params=get_all_route_params,
            get_one_route_params=get_one_route_params,
            create_route_params=create_route_params,
            update_route_params=update_route_params,
            delete_one_route_params=delete_one_route_params,
            delete_all_route_params=delete_all_route_params,
            verbose_name=verbose_name,
            verbose_name_plural=verbose_name_plural,
            delete_update_field=delete_update_field,
            memory=False,

            primary_key=self._primary_key,
            primary_key_type=self._primary_key_type,
            strict_authentication=strict_authentication,
            **kwargs
        )

    # ##### 完成路由 #####
    async def get_all_handler(
            self,
            pagination: PAGINATION,
            filters: list[str],
            orders: list[str],
            ids: list[int],
            payload: PayloadData | None = None
    ) -> GenericBaseResponse[GetAllData]:
        if not orders:
            orders = ['-level']
        return await super().get_all_handler(pagination, filters, orders, ids, payload)

    async def create_handler(self, model, payload: PayloadData | None = None) -> BaseResponse:
        # 预处理方法
        model_dict = await self.create_validator(model.model_dump())

        # 只取需要的字段的值
        db_model_data = {
            "column": {},
            "foreign_key": {},
            "many_to_many": {}
        }
        columns, foreign_key_columns, many_to_many_columns = self._get_columns(self.create_schema)
        for column in columns:
            if column.key in model_dict:
                db_model_data['column'][column.key] = model_dict.pop(column.key)
        for column in foreign_key_columns:
            if column.key in model_dict:
                db_model_data['foreign_key'][column.key] = model_dict.pop(column.key)
        for column in many_to_many_columns:
            if column.key in model_dict:
                db_model_data['many_to_many'][column.key] = model_dict.pop(column.key)

        # 获取主要显示字段信息
        invalid, main_key, main_value = await self.is_main_field_value_invalid(db_model_data['column'])

        if invalid:
            raise ValidationError(f"字段{main_key}的值{main_value}不允许以{await self.get_delete_prefix()}结尾")

        # 操作数据库
        db_model = await self.orm_create(db_model_data, model_dict, payload)
        # 查询当前数据，因为创建后总是获取id以及m2m字段有问题，因此单独使用sql语句查询
        value_from_primary_key = getattr(db_model, self._primary_key)
        model = await self.orm_get_one(value_from_primary_key, payload)
        # 生成响应模型实例
        response = BaseResponse(data=model)
        return response

    async def delete_all_handler(self, item_ids, payload: PayloadData | None = None) -> GenericBaseResponse[GetAllData]:
        """
        删除所有数据的处理方法
        :param item_ids: self._primary_key_type的列表类型，数据的id的集合
        :param payload:
        :return:
        """
        all_records, count, pagination = await self.orm_get_all_by_ids(ids=item_ids, payload=payload)

        # TODO 可能会修改原数据name等，是否需要另外copy一份需要再另外测试
        await self.orm_delete_all(item_ids, all_records, payload=payload)

        if count > 0:
            pagination.limit = count
            pagination.total = count

        pagination_data = PaginationData(index=pagination.index, limit=pagination.limit, total=pagination.total, offset=pagination.offset)
        data = GetAllData(items=all_records, pagination=pagination_data)
        response = GenericBaseResponse[GetAllData](data=data)
        return response

    async def get_one_handler(self, item_id, payload: PayloadData | None = None) -> BaseResponse:
        model = await self.orm_get_one(item_id, payload)
        response = BaseResponse(data=model)
        return response

    async def update_handler(self, item_id, model, payload: PayloadData | None = None) -> BaseResponse:
        # 只获取更新后的字段
        model = model.model_dump(exclude_unset=True, exclude={self._primary_key})
        invalid, main_key, main_value = await self.is_main_field_value_invalid(model)

        if invalid:
            raise SiteException(
                status_code=StatusMap.UPDATE_FAILED.http_code,
                response=BaseResponse(
                    code=StatusMap.UPDATE_FAILED.code,
                    success=StatusMap.UPDATE_FAILED.success,
                    message=f"字段{main_key}的值{main_value}不允许以{await self.get_delete_prefix()}结尾",
                )
            ) from None

        # 保存原数据，为进一步操作做准备
        # original_data = await self.orm_get_one(item_id, payload)

        await self.orm_update(item_id, model, payload)
        data = await self.orm_get_one(item_id, payload)
        response = BaseResponse(data=data)
        return response

    async def delete_one_handler(self, item_id, payload: PayloadData | None = None) -> BaseResponse:
        data = await self.orm_delete_one(item_id, payload)
        response = BaseResponse(data=data)
        return response

    # ###### 操作数据库 ######
    async def orm_get_all(
            self,
            pagination: PAGINATION = None,
            filters: dict[str, str | list[str]] = None,
            orders: list[str] = None,
            ids: list[int] = None,
            payload: PayloadData | None = None
    ) -> tuple[Sequence, int, PAGINATION]:
        if pagination is None:
            pagination = self.pagination()
        if filters is None:
            filters = {}
        if orders is None:
            orders = ['-level']

        # 格式化order参数
        orders_formatter = self._order_formatter(orders)
        # 获取查询语句
        all_statement, count_statement = await self.orm_get_all_statement(pagination, filters, orders_formatter, ids, payload)

        # 执行查询
        async with self.db_func().begin() as session:
            all_records = list()
            # execute the statement
            all_records_data = (await session.execute(all_statement)).scalars().all()
            for row in all_records_data:
                all_records.append(await self.format_query_data(row, session))
            count_records = (await session.execute(count_statement)).scalar()
        return all_records, count_records, pagination

    async def orm_create(self, db_model_data: dict[str, dict[str, Any]], other_data: dict[str, Any], payload: PayloadData | None = None) -> Model:
        # 数据操作session
        async with self.db_func().begin() as session:
            try:
                columns = db_model_data['column']
                # 创建普通字段的数据
                db_model = self.db_model(**columns)

                # 获取外键字段
                foreign_key_columns = db_model_data['foreign_key']
                # 获取多对多字段
                many_to_many_columns = db_model_data['many_to_many']
                # 创建外键和多对多字段
                await self.update_model(session, db_model, foreign_key_columns, many_to_many_columns)

                # 保存数据
                session.add(db_model)
                await session.flush()
                await session.commit()
                return db_model
            except IntegrityError as error:
                await session.rollback()
                code = error.orig.args[0]
                message = error.orig.args[1]
                match code:
                    case 1048:
                        result = re.match(r"Column '(.*)' cannot be null", message)
                        if result:
                            key = result.group(1)
                            response = BaseResponse(
                                code=StatusMap.DATA_VALIDATION_FAILED.code,
                                success=StatusMap.DATA_VALIDATION_FAILED.success,
                                message=f"字段{key}不能为null",
                            )
                        else:
                            response = BaseResponse(
                                code=StatusMap.DATA_VALIDATION_FAILED.code,
                                success=StatusMap.DATA_VALIDATION_FAILED.success,
                                message=message,
                            )
                        raise SiteException(status_code=StatusMap.CREATE_FAILED.http_code, response=response) from None
                    case 1062:
                        result = re.search(r"Duplicate entry '([^']*)' for key '([^']*)'", message)
                        if result:
                            key = result.group(2)
                            if "_@_" in key:
                                key = "_".join(key.split("_@_")[2:])
                            value = result.group(1)
                            response = BaseResponse(
                                code=StatusMap.PRIMARY_KEY_EXISTED.code,
                                success=StatusMap.PRIMARY_KEY_EXISTED.success,
                                message=f"字段{key}的值{value}已存在",
                            )
                        else:
                            response = BaseResponse(
                                code=StatusMap.PRIMARY_KEY_EXISTED.code,
                                success=StatusMap.PRIMARY_KEY_EXISTED.success,
                                message=message,
                            )
                        raise SiteException(status_code=StatusMap.PRIMARY_KEY_EXISTED.http_code, response=response) from None
                    case _:
                        response = BaseResponse(
                            code=StatusMap.CREATE_FAILED.code,
                            success=StatusMap.CREATE_FAILED.success,
                            message=message,
                        )
                        raise SiteException(status_code=StatusMap.CREATE_FAILED.http_code, response=response) from None
            except ValidationError as error:
                response = BaseResponse(
                    code=StatusMap.DATA_VALIDATION_FAILED.code,
                    success=StatusMap.DATA_VALIDATION_FAILED.success,
                    message=error.args[0] if error.args else StatusMap.DATA_VALIDATION_FAILED.message,
                )
                raise SiteException(status_code=StatusMap.CREATE_FAILED.http_code, response=response) from None
            except Exception as error:
                await session.rollback()
                request_id_var = REQUEST_CONTEXT["request_id"]
                request_id = request_id_var.get() if request_id_var else ''
                logger.exception(f"[RequestId: {request_id}] create {self.db_model.__name__} error: {error}")
                response = BaseResponse(
                    code=StatusMap.CREATE_FAILED.code,
                    success=StatusMap.CREATE_FAILED.success,
                    message=StatusMap.CREATE_FAILED.message,
                )
                raise SiteException(status_code=StatusMap.CREATE_FAILED.http_code, response=response) from None

    async def orm_delete_all(self, item_ids: list, items: Iterable[ModelBase] = None, payload: PayloadData | None = None):
        # 如果是真删除，则直接删除，否则将名称添加后缀 {时间戳[-6:]}_delete 并将 status 设置为 inactive
        # TODO 没有考虑超过数据库字符数量限制的情况
        if REAL_DELETE:
            delete_statement = delete(self.db_model).filter(getattr(self.db_model, self._primary_key).in_(item_ids)).returning(self.db_model)

            async with self.db_func().begin() as session:
                await session.execute(delete_statement)
                await session.commit()
        else:
            async with self.db_func().begin() as session:
                values = {'status': RecordStatus.OBSOLETE}
                for row in items:
                    await self.set_delete_show_name(row, values)
                    delete_statement = update(self.db_model).where(getattr(self.db_model, self._primary_key) == row[self._primary_key]).values(**values)
                    await session.execute(delete_statement)

                await session.commit()

    async def orm_get_all_by_ids(self, ids: [int] = None, orders: list[str] = None, payload: PayloadData | None = None) -> tuple[Sequence, int, PAGINATION]:
        if orders is None:
            orders = [f'-{getattr(self.db_model, self._primary_key).name}']

        # 普通用户只能查看 active 状态的数据
        status_list = [RecordStatus.ACTIVE.value]
        # 超级用户可以查看所有状态的数据
        if await is_superuser(payload):
            status_list.extend([RecordStatus.INACTIVE.value, RecordStatus.FROZEN.value])

        orders_formatter = self._order_formatter(orders)
        all_statement_by_ids = select(self.db_model).where(
            getattr(self.db_model, self._primary_key).in_(ids),
            getattr(self.db_model, 'status').in_(status_list)
        ).order_by(*orders_formatter)

        async with self.db_func().begin() as session:
            all_records = list()
            # execute the statement
            all_records_data = (await session.execute(all_statement_by_ids)).scalars().all()
            for row in all_records_data:
                all_records.append(await self.format_query_data(row, session))
        return all_records, len(all_records), PAGINATION()

    async def orm_get_one(self, item_id: int, payload: PayloadData | None = None) -> ModelBase:
        # 主键筛选提前定义出来
        filter_params = {self._primary_key: item_id}
        # 获取查询语句
        statement = await self.orm_get_one_statement(filter_params, payload)
        async with self.db_func().begin() as session:
            try:
                model = await session.execute(statement)
                model = await self.format_query_data(model.scalar_one(), session)
            except MultipleResultsFound:
                response = BaseResponse(
                    code=StatusMap.MULTIPLE_RESULTS_FOUND.code,
                    success=StatusMap.MULTIPLE_RESULTS_FOUND.success,
                    message=StatusMap.MULTIPLE_RESULTS_FOUND.message,
                )
                raise SiteException(status_code=StatusMap.MULTIPLE_RESULTS_FOUND.http_code, response=response) from None
            except NoResultFound:
                response = BaseResponse(
                    code=StatusMap.ITEM_NOT_FOUND.code,
                    success=StatusMap.ITEM_NOT_FOUND.success,
                    message=StatusMap.ITEM_NOT_FOUND.message,
                )
                raise SiteException(status_code=StatusMap.ITEM_NOT_FOUND.http_code, response=response) from None
        return model

    async def orm_update(self, item_id: int, model: dict, payload: PayloadData | None = None) -> None:
        if not model:
            return

        columns, foreign_key_columns, many_to_many_columns = self._get_columns()
        # 更新数据
        columns_data = {}
        for column in columns:
            if column.key in model:
                columns_data[column.key] = model.get(column.key)

        # 更新的外键字段
        foreign_key_data = {}
        for column in foreign_key_columns:
            if column.key in model:
                foreign_key_data[column.key] = model.get(column.key)

        # 更新的多对多字段
        many_to_many_data = {}
        for column in many_to_many_columns:
            if column.key in model:
                many_to_many_data[column.key] = model.get(column.key)

        async with self.db_func().begin() as session:
            try:
                # 获取原数据的查询语句
                # original_data = await session.execute(select(self.db_model).where(getattr(self.db_model, self._primary_key) == item_id))
                original_data_statement = select(self.db_model).where(getattr(self.db_model, self._primary_key) == item_id)
                # 添加外键的查询
                for field in foreign_key_data:
                    original_data_statement = original_data_statement.options(selectinload(getattr(self.db_model, field)))
                # 添加多对多的查询
                for field in many_to_many_data:
                    original_data_statement = original_data_statement.options(selectinload(getattr(self.db_model, field)))

                original_data = await session.execute(original_data_statement)
                original_data = original_data.scalar_one()

                # 更新外键和多对多字段
                await self.update_model(session, original_data, foreign_key_data, many_to_many_data)

                # 更新普通字段
                for field in columns_data:
                    setattr(original_data, field, columns_data[field])

                await session.commit()
            except Exception as error:
                await session.rollback()
                result = re.search(r"Duplicate entry '([^']*)' for key '([^']*)'", error.args[0])
                if result:
                    key = result.group(2)
                    if "_@_" in key:
                        key = "_".join(key.split("_@_")[2:])
                    value = result.group(1)
                    response = BaseResponse(
                        code=StatusMap.PRIMARY_KEY_EXISTED.code,
                        success=StatusMap.PRIMARY_KEY_EXISTED.success,
                        message=f"字段{key}的值{value}已存在",
                    )
                    raise SiteException(status_code=StatusMap.PRIMARY_KEY_EXISTED.http_code, response=response) from None
                request_id_var = REQUEST_CONTEXT["request_id"]
                request_id = request_id_var.get() if request_id_var else ''
                logger.error(f"[RequestId: {request_id}] update {self.db_model.__name__} error: {error}")
                response = BaseResponse(
                    code=StatusMap.UPDATE_FAILED.code,
                    success=StatusMap.UPDATE_FAILED.success,
                    message=StatusMap.UPDATE_FAILED.message,
                )
                raise SiteException(status_code=StatusMap.UPDATE_FAILED.http_code, response=response) from None

    async def orm_delete_one(self, item_id: int, payload: PayloadData | None = None) -> dict[str, Any] | None:
        data = await self.orm_get_one(item_id, payload)

        delete_statement = await self.orm_delete_one_statement(item_id, data, payload)
        async with self.db_func().begin() as session:
            try:
                await session.execute(delete_statement)
                await session.commit()
            except Exception as error:
                await session.rollback()
                request_id_var = REQUEST_CONTEXT["request_id"]
                request_id = request_id_var.get() if request_id_var else ''
                logger.error(f"[RequestId: {request_id}] delete {self.db_model.__name__} error: {error}")
                response = BaseResponse(
                    code=StatusMap.DELETE_FAILED.code,
                    success=StatusMap.DELETE_FAILED.success,
                    message=StatusMap.DELETE_FAILED.message,
                )
                raise SiteException(status_code=StatusMap.DELETE_FAILED.http_code, response=response) from None

        return data

    # ###### 生成语句 ######

    async def orm_get_all_statement(
            self,
            pagination: PAGINATION,
            filters: dict[str, str | list],
            orders: list,
            ids: list[int],
            payload: PayloadData | None = None
    ) -> tuple[Select, Select]:
        """
        生成查询语句
        :param pagination:
        :param filters:
        :param orders:
        :param ids:
        :param payload:
        :return:
        """
        _, foreign_key_columns, many_to_many_columns = self._get_columns()
        filter_value = await self.format_select_filter_params(filters, payload)

        # query count statement
        count_statement = select(func.count()).select_from(self.db_model).filter(*filter_value).distinct()
        # query all statement
        all_statement = select(self.db_model).filter(*filter_value).order_by(*orders).offset(pagination.offset).limit(pagination.limit).distinct()

        if ids:
            all_statement = all_statement.where(getattr(self.db_model, self._primary_key).in_(ids))
            count_statement = count_statement.where(getattr(self.db_model, self._primary_key).in_(ids))

        # 含有外键则添加外键查询
        for column in foreign_key_columns:
            all_statement = all_statement.join(column, isouter=True).options(selectinload(column))
        # 含有多对多关系则添加多对多查询
        for column in many_to_many_columns:
            all_statement = all_statement.join(column, isouter=True).options(selectinload(column))

        return all_statement, count_statement

    async def orm_get_one_statement(self, filters: dict[str, str | int | list], payload: PayloadData | None = None) -> Select:
        # 获取外键字段
        _, foreign_key_columns, many_to_many_columns = self._get_columns()
        # 管理员用户可以查看所有状态的数据，普通用户只能查看 active 状态的数据的逻辑处理
        filter_value = await self.format_select_filter_params(filters, payload)

        # 筛选语句
        statement = select(self.db_model).filter(*filter_value)
        # 添加外键查询
        for column in foreign_key_columns:
            statement = statement.join(column, isouter=True).options(selectinload(column)).distinct()
        # 添加多对多查询
        for column in many_to_many_columns:
            statement = statement.join(column, isouter=True).options(selectinload(column)).distinct()

        return statement

    async def orm_delete_one_statement(self, item_id: int, data: dict, payload: PayloadData | None = None) -> Delete | Update:
        # 如果是真删除，则直接删除，否则将名称添加后缀 _delete 并将 status 设置为 inactive
        if REAL_DELETE:
            delete_statement = delete(self.db_model).where(getattr(self.db_model, self._primary_key) == item_id).returning(self.db_model)
        else:
            values = {'status': RecordStatus.OBSOLETE}
            await self.set_delete_show_name(data, values)
            delete_statement = update(self.db_model).where(getattr(self.db_model, self._primary_key) == item_id).values(**values)

        return delete_statement

    # ###### 校验方法 ######
    async def create_validator(self, item: dict) -> dict:
        """
        校验创建数据， 返回校验后的数据
        :param item:
        :return:
        """
        return item

    async def is_main_field_value_invalid(self, model: dict) -> tuple[bool, str, str]:
        """
        检查主要字段的值是否不合法
        :param model:
        :return:
        """
        main_columns_key = ''
        main_columns_value = ''
        if 'name' in model:
            main_columns_key = 'name'
            main_columns_value = model['name']
        elif 'title' in model:
            main_columns_key = 'title'
            main_columns_value = model['title']
        elif self.delete_update_field and self.delete_update_field in model:
            main_columns_key = self.delete_update_field
            main_columns_value = model[self.delete_update_field]

        return main_columns_value.endswith(await self.get_delete_prefix()), main_columns_key, main_columns_value

    # ###### 其他工具方法 ######
    def _get_columns(self, schema: Type[BaseModel] = None) -> tuple[list, list, list]:
        if schema is None:
            schema = self.schema
        # 普通字段
        common_columns = []
        # 外键字段
        foreign_key_columns = []
        # 多对多字段
        many_to_many_columns = []

        for field in schema.model_fields.keys():
            if hasattr(self.db_model, field):
                db_model_field = getattr(self.db_model, field)
                if isinstance(db_model_field.property, ColumnProperty):
                    common_columns.append(db_model_field)
                elif isinstance(db_model_field.property, RelationshipProperty):
                    if db_model_field.property.direction.name == 'MANYTOMANY':
                        many_to_many_columns.append(db_model_field)
                    else:
                        foreign_key_columns.append(db_model_field)
        return common_columns, foreign_key_columns, many_to_many_columns

    def _order_formatter(self, orders: [str]) -> list:
        """
        格式化order参数
        :param orders:
        :return:
        """
        orders_formatter = []
        for order in orders:
            field, order_func = (order[1:], desc) if order.startswith('-') else (order, asc)

            if not hasattr(self.db_model, field):
                continue

            orders_formatter.append(order_func(getattr(self.db_model, field)))

        return orders_formatter

    async def format_select_filter_params(self, filters: dict[str, str | list], payload: PayloadData | None) -> list:
        """
        格式化查询参数
        :param payload:
        :param filters:
        :return:
        """
        status_list = [RecordStatus.ACTIVE.value, RecordStatus.INACTIVE.value, RecordStatus.FROZEN.value]
        # 只有超级用户可以查看所有状态的数据，其他用户不能看到逻辑删除的数据
        if 'status' in filters:
            # 含有 status 筛选条件并且不是超级用户，则需要筛除逻辑删除的数据
            if not await is_superuser(payload):
                if isinstance(filters['status'], list) and RecordStatus.OBSOLETE.value in filters['status']:
                    filters['status'].remove(RecordStatus.OBSOLETE.value)
                elif isinstance(filters['status'], str) and filters['status'] == RecordStatus.OBSOLETE.value:
                    # 保证查询不到任何数据
                    filters['status'] = 'unknown'
            # 是超级用户则全部放行即可
            # else:
            #     pass
        else:
            # 没有 status 筛选条件则默认只查询 active 状态的数据
            filters['status'] = status_list

        filter_value = list()
        for key in filters:
            if isinstance(filters[key], list):
                filter_value.append(getattr(self.db_model, key).in_(filters[key]))
            else:
                filter_value.append(getattr(self.db_model, key) == filters[key])

        return filter_value

    async def format_query_data(self, row, session) -> dict:
        """
        格式化查询数据，单条数据格式化
        :param row: 每一行数据
        :param session: 数据库session
        :return:
        """
        columns, foreign_key_columns, many_to_many_columns = self._get_columns()
        # 整理数据
        row_dict = dict()

        # 获取普通字段的值
        for field in columns:
            if hasattr(row, field.key):
                row_dict[field.key] = getattr(row, field.key)

        # 获取外键字段的值
        for field in foreign_key_columns:
            if hasattr(row, field.key):
                foreign_value = getattr(row, field.key)
                if foreign_value is None:
                    row_dict[field.key] = 0
                else:
                    if isinstance(foreign_value, Iterable):
                        row_dict[field.key] = [child.id for child in getattr(row, field.key)]
                    else:
                        row_dict[field.key] = foreign_value

        # 获取多对多字段的值
        for field in many_to_many_columns:
            if hasattr(row, field.key):
                many_to_many_value = getattr(row, field.key)
                if many_to_many_value is None:
                    row_dict[field.key] = []
                else:
                    row_dict[field.key] = [child.id for child in getattr(row, field.key)]
        return row_dict

    async def update_model(self, session, model, foreign_key: dict[str, int], many_to_many: dict[str, list[int]]):
        # 更新外键字段
        for column in foreign_key:
            # 获取外键的模型和id
            foreign_key_model = getattr(self.db_model, column).mapper.class_
            foreign_key_id = foreign_key[column]
            # 获取对应外键的值
            foreign_key_data = await session.execute(select(foreign_key_model).where(foreign_key_model.id == foreign_key_id))
            foreign_key_data = foreign_key_data.scalar_one_or_none()
            # 外键赋值
            if foreign_key_data:
                setattr(model, column, foreign_key_data)
        # 更新多对多字段
        for column in many_to_many:
            # 获取多对多的模型和id
            many_to_many_model = getattr(self.db_model, column).mapper.class_
            many_to_many_ids = many_to_many[column]

            current_many_to_many_ids = [m2m.id for m2m in getattr(model, column)]
            add_m2m_ids = set(many_to_many_ids) - set(current_many_to_many_ids)
            remove_m2m_ids = set(current_many_to_many_ids) - set(many_to_many_ids)

            all_need_m2m_ids = add_m2m_ids | remove_m2m_ids

            if not all_need_m2m_ids:
                continue

            # 获取对应多对多的值
            many_to_many_data = await session.execute(select(many_to_many_model).where(many_to_many_model.id.in_(all_need_m2m_ids)))
            many_to_many_data = many_to_many_data.scalars().all()
            # 多对多赋值
            for m2m in many_to_many_data:
                if m2m.id in add_m2m_ids:
                    getattr(model, column).append(m2m)
                if m2m.id in remove_m2m_ids:
                    getattr(model, column).remove(m2m)
